#!/bin/env perl 
use strict;
use warnings;
=head1 NAME 

create_ecenter_db.pl - create ecenter db

=head1 DESCRIPTION

create Ecenter db or update it with partitions, must be used under MySQL 5.5 or newer !!!

=head1 OPTIONS

=head2 --user=<string>

username to connect to the ecenter data db 
Default: ecenter

=head2 --root_pass=<string>

root password to connect to the ecenter data db
Default:read from /root/ecenter/etc/my_db 

=head2 --trunk=<string>

absolute path to the trunk dir ( root for the ecenter data API)
Default: /home/netadmin/ecenter/trunk
 
=head2 --db=<string>

database name of the ecenter data db
Default: ecenter_data

=head2 --pass=<string>

password to connect to the ecenter data db  as ecenter user
Default: read from /etc/my_ecenter

=head2 --fresh

drop old db and create a new one with API - usualy done only once
Default: not set

=head2 --db_template

filename of the template file to create db - without partitions - but for the API bindings
Default: $OPTIONS{trunk}/collector/ecenter_db_sql.tmpl

=head2 --db_partition_template

filename of the template file to create  DB with intial partitions and no FKs, no APi will be created
Default: $OPTIONS{trunk}/collector/ecenter_db_partition_sql.tmpl

=head2 --add_partitions_template

filename of the template file to add new partition into the partions enabled DB 
Default: undef

=cut

use POSIX qw(strftime);
use Template;
use Carp;
use Date::Manip::Date;
use English qw( -no_match_vars );
use Getopt::Long;
use Pod::Usage;
use DBI;
use DBIx::Class::Schema::Loader qw/ make_schema_at /;

my %OPTIONS; 
my @string_option_keys = qw/user root_pass db trunk pass db_template db_partition_template add_partitions_template/;
#
# 10M the MAX
#
my $MAX_PARTITION = 10000000;
#
#  increment for counter
#
my $PARTITION_INCR = 1000000;

GetOptions( \%OPTIONS,
            map("$_=s", @string_option_keys),
            qw/debug help fresh/,
) or pod2usage(1);

pod2usage(1) if $OPTIONS{help};
 
unless($OPTIONS{pass}) {
    $OPTIONS{pass} = `cat /etc/my_ecenter`;
    chomp $OPTIONS{pass};
}
unless($OPTIONS{root_pass}) {
    $OPTIONS{root_pass} = `cat /root/ecenter/etc/my_db`;
    chomp $OPTIONS{root_pass};
}
croak(" no root password provided !!!" ) unless $OPTIONS{root_pass};
$OPTIONS{db} ||= 'ecenter_data';
$OPTIONS{user} ||= 'ecenter';

my $today = strftime("%Y%m", localtime());
$OPTIONS{trunk} ||= '/home/netadmin/ecenter/trunk';
$OPTIONS{db_template} ||= "$OPTIONS{trunk}/collector/ecenter_db_sql.tmpl";
$OPTIONS{db_partition_template} ||= "$OPTIONS{trunk}/collector/ecenter_db_partition_sql.tmpl";
 
$OPTIONS{preserve} = $OPTIONS{fresh}?'':' if not exists ';

my $template = Template->new({ABSOLUTE => 1});
croak(" Template   $OPTIONS{db_partition_template} is missing !!!" ) unless -e  $OPTIONS{db_partition_template};
croak(" Template   $OPTIONS{db_template} is missing !!!" ) unless -e  $OPTIONS{db_template};
if($OPTIONS{add_partitions_template}) {
    croak(" Template   $OPTIONS{add_partitions_template} is missing !!!" )
        unless -e  $OPTIONS{add_partitions_template};
    my $sql_file = "$OPTIONS{trunk}/collector/sql/ecenter_add_partitions";
    `mv $sql_file.sql  $sql_file.$today` 
        if -e "$sql_file.sql";
    my $dbh = DBI->connect("dbi:mysql:database=$OPTIONS{db}", $OPTIONS{user}, $OPTIONS{pass}) 
        or croak "Connection Error: $DBI::errstr"; 
    my ($last_partition) = $dbh->selectrow_array('select partition_counter from last_partition order by  partition_counter desc limit 1');
    $last_partition ||=  $MAX_PARTITION;
    $last_partition += $PARTITION_INCR;
    my $name =  int($last_partition/$PARTITION_INCR);
    $template->process( $OPTIONS{add_partitions_template}, 
                        { dbname => $OPTIONS{db} , 
			  last_partition => $last_partition,
			  partition => {name => "p_$name\_data", value => $last_partition} }, 
		        "$sql_file.sql"
		      ) 
		      or croak("$OPTIONS{add_partitions_template} processing failed: " . $template->error);
    if(system("/usr/local/mysql/bin/mysql -u root -p'$OPTIONS{root_pass}' < $sql_file.sql")) {
        croak("Failed to add partitions in the $OPTIONS{db} db: $OS_ERROR $ERRNO");
    }
    exit 0;
}
my $ecenter_db_sql = "$OPTIONS{trunk}/collector/sql/ecenter_db";
my $ecenter_partition_db_sql = "$OPTIONS{trunk}/collector/sql/ecenter_db_partitions";
##
##  backing up old sql files
##
`mv $ecenter_db_sql.sql  $ecenter_db_sql.$today`  if -e "$ecenter_db_sql.sql";
`mv $ecenter_partition_db_sql.sql  $ecenter_partition_db_sql.$today`  if -e "$ecenter_partition_db_sql.sql";
#
#   create SQL file for the DB
#
$template->process(  $OPTIONS{db_template}, 
                    { dbname => $OPTIONS{db}, preserve => $OPTIONS{preserve},
		      user => $OPTIONS{user}, pass =>  $OPTIONS{pass} }, 
		    "$ecenter_db_sql.sql") 
		         or croak("$ecenter_db_sql.sql processing failed: " . $template->error);

####my $dbh = DBI->connect("dbi:mysql:database=$OPTIONS{db}", 'root', $OPTIONS{root_pass}) or croak(" Couldnt connect to db $DBI::errstr");
#
#    create DB
#
if(system("/usr/local/mysql/bin/mysql -u root -p'$OPTIONS{root_pass}' <  $ecenter_db_sql.sql")) {
    croak("Failed to load  $ecenter_db_sql.sql into the $OPTIONS{db} db: $OS_ERROR $ERRNO");
}
#    create API
#
make_schema_at(
      'Ecenter::DB',
      {  really_erase_my_files =>  ($OPTIONS{fresh}?1:0), 
         dump_directory => "$OPTIONS{trunk}/frontend_components/ecenter_data/lib" },
       [ "dbi:mysql:database=$OPTIONS{db}", $OPTIONS{user} ,  $OPTIONS{pass} ],
);
#   drop FKs and create/re-org partitions
#

$template->process(  $OPTIONS{db_partition_template}, 
                    { dbname => $OPTIONS{db},  preserve => $OPTIONS{preserve},  
		      user => $OPTIONS{user}, pass =>  $OPTIONS{pass},
		      partitions =>  get_partitions(),
		    }, 
		    "$ecenter_partition_db_sql.sql") 
		         or croak("$ecenter_partition_db_sql.sql processing failed: " . $template->error);

#
#    fix DB
#
if(system("/usr/local/mysql/bin/mysql -u root -p'$OPTIONS{root_pass}' <  $ecenter_partition_db_sql.sql")) {
    croak("Failed to create in the db: $OS_ERROR $ERRNO");
}
exit 0;
#
#    get_partitions
#       accepts single parameter 
#      returns array ref with hashref elements - name - name of the partition and value - range limit for the partition
#  
#
sub get_partitions {
    my ($start_time) = @_; 
    $start_time ||= time();
    my @dates = ();
    for(my $date= 1 ;$date<= 10;$date++) {
	push @dates, {name => "p_$date\_data", value=> $date*1000000};
    }
    return \@dates;
}
 
=head1 VERSION

$Id: $

=head1 AUTHOR

Maxim Grigoriev, maxim_at_fnal_dot_gov 

=head1 LICENSE

You should have received a copy of the  Fermitools license
with this software.  If not, see <http://fermitools.fnal.gov/about/terms.html>

=head1 COPYRIGHT

Copyright (c) 2010-2011, Fermitools

All rights reserved.

=cut
